create or replace package pk_sms_operate is

  -- Author  : HOP.DANG
  -- Created : 2013-01-23 10:14:11 AM
  -- Purpose : For reading MO XML file, calling SendMT function in Gapit web service
  
  -- Public type declarations
  -- type <TypeName> is <Datatype>;
  
  -- Public constant declarations
    v_mo_directory constant varchar2(255) := 'd:\sms_data\mo\';

  -- Public variable declarations
  -- <VariableName> <Datatype>;

  -- Public function and procedure declaratns
  procedure pr_getfilelist (p_directory varchar2);
  procedure pr_parsexml_mo (p_directory varchar2);
  
end pk_sms_operate;

/

create or replace package body pk_sms_operate is

  -- Private type declarations
  --type <TypeName> is <Datatype>;
  
  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  --<VariableName> <Datatype>;

  -- Function and procedure implementations
  procedure pr_getfilelist(p_directory varchar2) as language java
  name 'DirList.getList( java.lang.String )';
  
  --/Parse Xml files and insert to MO table.
  procedure pr_parsexml_mo (p_directory varchar2) is
    v_element     varchar2(255);
    v_nodevalue   varchar2(255) := null;
    v_rows        integer;
    v_xmlfile     utl_file.file_type;
    
    cursor v_f_curs is
    select  filename
    from    dir_list;
    
    v_filename    dir_list.filename%type;
  begin
    open v_f_curs;
    loop
      fetch v_f_curs into v_filename;
      if v_f_curs%notfound then
        dbms_output.put_line ('there is no any files');
        exit;
      else
        begin
          v_xmlfile := utl_file.fopen('MYDIR' ,v_filename,'r');
          loop
            begin
              utl_file.get_line(v_xmlfile, v_element);
              exception
                when no_data_found then
                  utl_file.fclose(v_xmlfile);
                  exit;
            end;
            dbms_output.put_line (v_element);
          end loop;
         exception
          when others then
            dbms_output.put_line( p_directory || v_filename);
            exit;
        end;    
      end if;
    end loop;
  end;
end pk_sms_operate;
